import { query, queryOne, execute, queryForMap } from '../connector';
import { ProFormColumnsType } from "@ant-design/pro-components";
// import { DataSourceOptions } from "@/pages/rrg01/create/components";

// import { DataSource, DatabaseType, ValueType, getDbTypeOptions, getValueType } from '@/services/types';
import { ConnectorDataSource as DataSource, ConnectorDatabaseType as DatabaseType, ValueType } from '@/services';
import { stringify } from 'querystring';

export interface TableSchema {
    name: string,
    collation?: string,
    comment?: string,
}

export const getTables = async (datasource: DataSource): Promise<any> => {
    //
    const { url, database_type } = datasource;

    console.log(database_type, '===');

    let data: Array<TableSchema> = [];

    if (database_type === 'mysql') {
        const { header, values } = await query(datasource, "select t.table_schema, t.table_name, t.table_type, t.engine,t.table_collation, t.table_comment from information_schema.tables t where table_schema = database()", [], {});
        data = values.map((item, index) => {
            return {
                name: item[1]
            };
        });
    } else if (database_type === 'sqlite') {
        const { header, values } = await query(datasource, "select name, tbl_name, rootpage, sql from sqlite_master where type = 'table'", [], {});
        data = values.map((item, index) => {
            return {
                name: item[1]
            };
        });
    } else {

    }

    console.log(data);
    return data;
}



export const getTablesDDL = async (datasource: DataSource): Promise<Array<string>> => {
    //
    const { url, database_type } = datasource;

    let data: Array<string> = [];

    if (database_type === 'mysql') {
        let tables = await getTables(datasource);
        for (let i = 0; i < tables.length; i++) {
            let table = tables[i].name;
            const { header, value } = await queryOne(datasource, `show create table ${table}`, [], {});
            data.push(value[1]);
        }
    } else if (database_type === 'sqlite') {
        let tables = await getTables(datasource);
        for (let i = 0; i < tables.length; i++) {
            let table = tables[i].name;
            const { header, value } = await queryOne(datasource, `select name, tbl_name, rootpage, sql from sqlite_master where type = 'table' and tbl_name = '${table}'`, [], {});
            data.push(value[3]);
        }
    } else {

    }
    console.log(data);
    return data;
}

export const getTableDDL = async (datasource: DataSource, table: string): Promise<any> => {
    //
    const { url, database_type } = datasource;

    let data: Array<TableSchema> = [];

    if (database_type === 'mysql') {
        const { header, value } = await queryOne(datasource, `show create table ${table}`, [], {});
        return value[1];
    } else if (database_type === 'sqlite') {
        const { header, value } = await queryOne(datasource, `select name, tbl_name, rootpage, sql from sqlite_master where type = 'table' and tbl_name = '${table}'`, [], {});
        return value[3];
    } else {

    }

    console.log(data);
    return data;
}


export const getTableColumns = async (datasource: DataSource, table: string)
    : Promise<Array<{ name: string, columnType: string, valueType: ValueType, valueLength: number }>> => {
    //
    const { url, database_type } = datasource;

    let data: Array<TableSchema> = [];

    if (database_type === 'mysql') {
        const values = await queryForMap(datasource, `select c.column_name, c.ordinal_position, c.data_type, c.character_maximum_length, c. character_octet_length, c.numeric_precision, c.numeric_scale, c.column_type  from information_schema.COLUMNS c
            WHERE c.TABLE_SCHEMA = database() and c.table_name = '${table}'  order by c.ordinal_position`, [], {});

        console.log(values);
        return values.map((item) => {
            let valueType = getValueType(database_type, item.data_type, item);

            return {
                name: item.column_name,
                columnType: item.column_type,
                valueType: valueType.type,
                valueLength: valueType.length
            }
        });
    } else if (database_type === 'sqlite') {
        const { header, values } = await query(datasource, `PRAGMA table_info(${table})`, [], {});

        console.log(header, values);
        return values.map((item) => {
            let valueType = getValueType(database_type, item[2], item);

            return {
                name: item[1],
                columnType: item[2],
                valueType: valueType.type,
                valueLength: valueType.length
            }
        });
    } else {

    }

    console.log(data);
    return [];
}

export const getTableRows = async (
    datasource: DataSource,
    table: string,
    conditions: any,
    orders: any,
    page: number,
    pageSize: number,
): Promise<{ header: Array<any>, values: Array<Array<any>>, total: number, page: number, pageSize: number }> => {
    //
    const { url, database_type } = datasource;

    let data = [];

    if (database_type === 'mysql') {
        let total = 0;
        {
            const { header, value } = await queryOne(datasource, `select count(1) from ${table} `, [], {});
            total = value[0];
        }
        const values: any[] = await queryForMap(datasource, `select _rowid, a.* from ${table} a LIMIT ${page * pageSize}, ${pageSize}`, [], {});

        console.log(values);
        return {
            header: [],
            values: values,
            total,
            page,
            pageSize,
        };
    } else if (database_type === 'sqlite') {
        let total = 0;
        {
            const { header, value } = await queryOne(datasource, `select count(1) from ${table} `, [], {});
            total = value[0];
        }

        const values: any[] = await queryForMap(datasource, `select rowid _rowid, * from ${table} LIMIT (${page} * ${pageSize}), ${pageSize}`, [], {});

        console.log(values);
        return {
            header: [],
            values: values,
            total,
            page,
            pageSize,
        };
    } else {

    }
    console.log(data);
    return {
        total: 0,
        page,
        pageSize,
        values: [],
        header: []
    };
}


export const getTableMapRows = async (
    datasource: DataSource,
    table: string,
    conditions: any,
    orders: any,
    page: number,
    pageSize: number,
): Promise<{ values: Array<{ [key: string]: any }>, total: number, page: number, pageSize: number }> => {
    //
    const { url, database_type } = datasource;

    let data: Array<TableSchema> = [];

    if (database_type === 'mysql') {
        let total = 0;
        {
            const { header, value } = await queryOne(datasource, `select count(1) from ${table} `, [], {});
            total = value[0];
        }
        const values = await queryForMap(datasource, `select _rowid, a.* from ${table} a LIMIT ${page * pageSize}, ${pageSize}`, [], {});

        console.log(values);
        return {
            values,
            total,
            page,
            pageSize,
        };
    } else if (database_type === 'sqlite') {
        let total = 0;
        {
            const { header, value } = await queryOne(datasource, `select count(1) from ${table} `, [], {});
            total = value[0];
        }

        const values = await queryForMap(datasource, `select rowid _rowid, * from ${table} LIMIT (${page} * ${pageSize}), ${pageSize}`, [], {});

        console.log(values);
        return {
            values,
            total,
            page,
            pageSize,
        };
    } else {

    }
    console.log(data);
    return {
        total: 0,
        page,
        pageSize,
        values: data
    };
}


export const alterTableComment = async (datasource: DataSource, table: string, comment: string): Promise<any> => {
    const { url, database_type } = datasource;

    if (database_type === 'mysql') {
        let res = execute(datasource, `alter table ${table} comment '${comment}'`, [], {});
        console.log(res);
        return res;
    }
};

export const queryTableComment = async (datasource: DataSource, table: string): Promise<string> => {
    const { url, database_type } = datasource;

    if (database_type === 'mysql') {
        let res = await queryOne(datasource, `select t.table_comment from information_schema.tables t where table_schema = database() and table_name = ?`, [table], {});
        console.log(res.value);
        return res.value[0] + '';
    }
    return '';
}


export const columnRename = async (datasource: DataSource, table: string, oldName: string,
    newName: string, options?: { [key: string]: any }): Promise<boolean> => {

    const { url, database_type } = datasource;

    try {
        if (database_type === 'mysql') {
            let res = await execute(datasource, `ALTER TABLE ${table} RENAME COLUMN ${oldName} TO ${newName}`, [], {});
        } else if (database_type === 'sqlite') {
            let res = await execute(datasource, `ALTER TABLE ${table} RENAME COLUMN ${oldName} TO ${newName}`, [], {});
        }
        return true;
    } catch (err) {
        console.log(err);
    }
    return false;
}


export const columnDelete = async (datasource: DataSource, table: string, column: string, options?: { [key: string]: any }): Promise<boolean> => {

    const { url, database_type } = datasource;

    try {
        if (database_type === 'mysql') {
            let res = await execute(datasource, `ALTER TABLE ${table} DROP ${column}`, [], {});
        } else if (database_type === 'sqlite') {
            let res = await execute(datasource, `ALTER TABLE ${table} DROP ${column}`, [], {});
        }
        return true;
    } catch (err) {
        console.log(err);
    }
    return false;
}

export const columnUpdateValue = async (
    datasource: DataSource,
    table: string,
    column: string,
    value: any,
    options: { [key: string]: any }
): Promise<boolean> => {

    const { url, database_type } = datasource;

    try {
        if (database_type === 'mysql') {
            let rowid = options['_rowid'];
            let res = await execute(datasource, `UPDATE ${table} SET ${column} = ? WHERE _rowid = ? `, [value, rowid], {});
        } else if (database_type === 'sqlite') {
            let rowid = options['_rowid'];
            let res = await execute(datasource, `UPDATE ${table} SET ${column} = ? WHERE rowid = ? `, [value, rowid], {});
        }
        return true;
    } catch (err) {
        console.log(err);
    }
    return false;
}

export const createTable = async (datasource: DataSource, options: { [key: string]: any }) => {
    const { url, database_type } = datasource;
    try {
        let tableName = options.name;

        if (database_type === 'mysql') {
            let res = await execute(datasource,
                `CREATE TABLE ${tableName} (
                    id int(10) unsigned NOT NULL AUTO_INCREMENT, 
                    PRIMARY KEY (id) 
                )ENGINE=InnoDB AUTO_INCREMENT=173 DEFAULT CHARSET=utf8mb4;`, [], {});
        } else if (database_type === 'sqlite') {
            let res = await execute(datasource,
                `CREATE TABLE ${tableName} (
                    id INTEGER PRIMARY KEY AUTOINCREMENT
                );`, [], {});
        }
        return true;
    } catch (err) {
        console.log(err);
    }
    return false;
}

export const createTableColumn = async (datasource: DataSource, table: string, options: { [key: string]: any }) => {
    const { url, database_type } = datasource;
    try {
        let columnName = options.name;
        let columnType = options.type;
        let columnTypeLength = options.typeLength;

        let typeStr = columnType;
        if (columnTypeLength && columnTypeLength > 0) {
            typeStr = `${columnType}(${columnTypeLength})`
        }

        if (database_type === 'mysql') {
            let res = await execute(datasource,
                `ALTER TABLE ${table} ADD COLUMN ${columnName} ${typeStr} DEFAULT NULL COMMENT '';`, [], {});
        } else if (database_type === 'sqlite') {
            let res = await execute(datasource,
                `ALTER TABLE ${table} ADD COLUMN ${columnName} ${columnType} DEFAULT 0;`, [], {});
        }
        return true;
    } catch (err) {
        console.log(err);
    }
    return false;
}


export const dropTable = async (datasource: DataSource, table: string, options?: { [key: string]: any }) => {
    const { url, database_type } = datasource;
    try {
        if (database_type === 'mysql') {
            let res = await execute(datasource, `DROP TABLE ${table}`, [], {});
        } else if (database_type === 'sqlite') {
            let res = await execute(datasource, `DROP TABLE ${table}`, [], {});
        }
        return true;
    } catch (err) {
        console.log(err);
    }
    return false;
}


export const getCreateTableFormSchema = async (dbType: DatabaseType): Promise<ProFormColumnsType<DataSourceOptions>[]> => {

    if (dbType === 'mysql') {
        return [
            {
                title: '表名',
                dataIndex: 'name',
                formItemProps: {
                    rules: [
                        {
                            required: true,
                            message: '此项为必填项',
                        },
                    ],
                },
                initialValue: ''
            }
        ];
    }
    return [
        {
            title: '表名',
            dataIndex: 'name',
            formItemProps: {
                rules: [
                    {
                        required: true,
                        message: '此项为必填项',
                    },
                ],
            },
            initialValue: ''
        }
    ];
}


export const getCreateColumnFormSchema = async (dbType: DatabaseType): Promise<ProFormColumnsType<DataSourceOptions>[]> => {

    let common: ProFormColumnsType<DataSourceOptions>[] = [
        {
            title: '列名',
            dataIndex: 'name',
            formItemProps: {
                rules: [
                    {
                        required: true,
                        message: '此项为必填项',
                    },
                ],
            },
            initialValue: ''
        },
        {
            title: '类型',
            dataIndex: 'type',
            valueType: 'select',
            valueEnum: getDbTypeOptions(dbType).map((item, index) => {
                return {
                    text: item.label,
                    status: item.value,
                };
            }).reduce((previousValue, currentValue, currentIndex, array) => {
                previousValue[currentValue.status] = currentValue;
                return previousValue;
            }, {}),
            formItemProps: {
                rules: [
                    {
                        required: true,
                        message: '此项为必填项',
                    },
                ],
            },
            initialValue: ''
        }
    ];
    if (dbType === 'mysql') {
        //typeLength
        return common.concat([
            {
                title: '类型长度',
                dataIndex: 'typeLength',
                formItemProps: {
                    rules: [],
                },
                initialValue: ''
            },
        ]);
    }
    return common;
}


export const getDbTypeOptions = (database_type: DatabaseType): Array<{ value: string, label: any }> => {
    if (database_type === 'mysql') {
        const types = ['int', 'tinyint', 'smallint', 'varchar', 'text', 'bigint', 'bit', 'blob', 'char', 'timestamp', 'date', 'time',
            'datetime', 'double', 'float', 'json', 'longblob', 'longtext', 'mediumblob', 'mediumint', 'mediumtext', 'tinytext', 'set', 'enum', 'decimal'];
        return types.map((t) => {
            return { value: t, label: t.toUpperCase() };
        });
    } else if (database_type === 'sqlite') {
        const types = ['integer', 'real', 'text', 'blob', 'int', 'tinyint', 'timestamp', 'date', 'datetime'];
        return types.map((t) => {
            return { value: t, label: t.toUpperCase() };
        });
    }
    return [];
}

export interface DataSourceOptions{
    
}

export const getValueType = (database_type: DatabaseType, columnType: string, options: { [key: string]: any }): { type: ValueType, length: number } => {

    if (database_type === 'mysql') {
        return getValueTypeMysql(columnType, options);
    } else if (database_type === 'sqlite') {
        if (columnType.toUpperCase() === 'TIMESTAMP') {
            return {
                type: ValueType.Timestamp,
                length: 0
            };
        }
    }
    return {
        type: ValueType.String,
        length: 0
    };
}



export const getValueTypeMysql = (columnType: string, options: { [key: string]: any }): { type: ValueType, length: number } => {

    if (columnType === 'int') {
        return {
            type: ValueType.Number,
            length: 0
        }
    }
    return {
        type: ValueType.String,
        length: 0
    };
}